import pymongo
from datetime import datetime
from gridfs import GridFS
from bson.objectid import ObjectId
import cv2, base64, json
import numpy as np
from hashlib import md5


class MongoGridFS(object):
    def __init__(self):
        # dbURL = 'mongodb://localhost:27017/'
        dbURL = 'mongodb://synjones:synjones2021@47.93.57.111:27017/'
        self.dbName = 'synUpdate'
        self.mongo_client = pymongo.MongoClient(dbURL)
        self.mongo_client.admin.command('ismaster')
        print('db connect succes -----------------------')

    def upLoadFile(self, file_coll, file_name, version_desc):
        '''
        上传文件
        :param file_coll: 集合名
        :param file_name: 文件名(自定义属性字段)
        :param data_link: 文件链接(自定义属性字段)
        :return: 返回 files_id
        '''
        db = self.mongo_client[self.dbName]
        fs = GridFS(db, collection=file_coll)
        # filename字段是固定的，可增加其他字段
        filter_condition = {
            "filename": file_name,
            "version_desc": version_desc
        }
        bExist = fs.exists(filter_condition)
        files_id = '0'
        if bExist is True:
            print(f'file={filter_condition}已存在')
        else:
            with open(file_name, 'rb') as f:
                file_data = f.read()
                # files_id = file_coll.files._id = file_coll.chunks.files_id
                files_id = fs.put(data=file_data, **filter_condition)

        return files_id

    def downLoadFile(self, file_coll, file_name, out_name, ver=-1):
        '''
        按文件名下载
        :param file_coll: 集合名
        :param file_name: 文件名
        :param out_name: 下载下来的文件名
        :param ver: 版本号，默认-1表示最近一次的记录
        :return
        '''
        db = self.mongo_client[self.dbName]
        gridfs_col = GridFS(db, collection=file_coll)
        file_data = gridfs_col.get_version(filename=file_name,
                                           version=ver).read()
        with open(out_name, 'wb') as f:
            f.write(file_data)

    def downLoadFilebyId(self, file_coll, _id, out_name):
        '''
        按文件id获取文件
        :param file_coll: 集合名
        :param _id: 文件id ==> files_id
        :param out_name: 下载后的文件名称
        :return 文件名
        '''
        db = self.mongo_client[self.dbName]
        gridfs_col = GridFS(db, collection=file_coll)
        O_Id = ObjectId(_id)
        gf = gridfs_col.get(file_id=O_Id)
        file_data = gf.read()
        with open(out_name, 'wb') as f:
            f.write(file_data)

        return gf.filename

    def tt(self, file_coll):
        db = self.mongo_client[self.dbName]
        fs = GridFS(db, collection=file_coll)
        # 判断是否存在升级包
        gf = fs.list()
        print('------list=', gf)
        last_version = fs.get_last_version()
        print('-----last_version=', last_version)
        filename = last_version.filename
        print('判断是存在升级包的个数=', len(gf), '文件名=', filename)
        tt = fs.find()
        for v in tt:
            print(v.filename, '---', v.md5)

    def test1(self):
        start_time = '2021-11-02 09:00:00'
        end_time = '2021-11-02 23:00:00'
        start = datetime.strptime(start_time, "%Y-%m-%d %H:%M:%S")
        end = datetime.strptime(end_time, "%Y-%m-%d %H:%M:%S")
        collection = self.mongo_client['synPosBill']['billList']
        cursorcount = collection.aggregate([
            {
                '$match': {
                    'createTime': {
                        '$gte': start,
                        '$lte': end
                    },
                    'diningNo': '1',
                    'stallNo': '1',
                    'deviceNo': '1',
                    'billState': 1
                }
            },
            {
                '$group': {
                    '_id': '$diningNo',
                    'total_bill_count': {  # 总单数 
                        '$sum': 1
                    },
                    'total_amount_count': {  # 总钱数 单位（分）
                        '$sum': '$chargeAmount'
                    },
                    'consum_of_person': {  # 人均消费 单位（分）
                        '$avg': '$chargeAmount'
                    }
                }
            }
        ])
        for v in cursorcount:
            print('----------------------', v)

    def test2(self):
        start_time = '2021-11-02 09:00:00'
        end_time = '2021-11-02 23:00:00'
        start = datetime.strptime(start_time, "%Y-%m-%d %H:%M:%S")
        end = datetime.strptime(end_time, "%Y-%m-%d %H:%M:%S")
        # start = datetime.strptime(start_time, "%Y-%m-%d %H")
        # end = datetime.strptime(end_time, "%Y-%m-%d %H")
        collection = self.mongo_client['synPosBill']['billList']
        match_dict = {
            "$match": {
                'createTime': {
                    '$gte': start,
                    '$lte': end
                },
                'diningNo': '1',
                'stallNo': '1',
                'deviceNo': '1',
                'billState': 1
            }
        }
        unwind_dict = {'$unwind': "$dishList"}
        group_dict = {
            '$group': {
                '_id': {
                    'Id': '$dishList.dishId'
                },
                'dishTotalCount': {
                    '$sum': '$dishList.dishCount'
                },
                'dishName': {
                    '$addToSet': '$dishList.dishName'
                },
                'dishPrice': {
                    '$addToSet': '$dishList.dishPrice'
                },
                'dishUnit': {
                    '$addToSet': '$dishList.dishUnit'
                }
            }
        }
        sort_dict = {'$sort': {'dishTotalCount': -1}}
        project_dict = {
            '$project': {
                '_id': 0,
                'dishId': '$_id.Id',
                'dishCount': '$dishTotalCount',
                'dishName': '$dishName',
                'dishPrice': '$dishPrice',
                'dishUnit': '$dishUnit'
            }
        }
        cursorcount = collection.aggregate(
            [match_dict, unwind_dict, group_dict, sort_dict, project_dict])
        for v in cursorcount:
            print(v)

    def test3(self):
        start_time = '2021-11-02 09:00:00'
        end_time = '2021-11-02 23:00:00'
        start = datetime.strptime(start_time, "%Y-%m-%d %H:%M:%S")
        end = datetime.strptime(end_time, "%Y-%m-%d %H:%M:%S")
        # start = datetime.strptime(start_time, "%Y-%m-%d %H")
        # end = datetime.strptime(end_time, "%Y-%m-%d %H")
        print('----', type(start), start)
        collection = self.mongo_client['synPosBill']['billList']
        match_dict = {
            "$match": {
                'createTime': {
                    '$gte': start,
                    '$lte': end
                },
                'diningNo': '1',
                'stallNo': '1',
                'deviceNo': '1',
                'billState': 1
            }
        }
        project_dict = {
            "_id": 0,
            'new_time_stamp': {
                '$substrBytes': ["$createTime", 0, 10]
            },
            "ddd": 1
        }
        group_dict = {'$group': {'_id': '$new_time_stamp'}}
        cursorcount = collection.aggregate([match_dict, project_dict])
        for v in cursorcount:
            print(v)

    def test4(self):
        dishList = []
        diningNos = ['1', '2']
        res = self.mongo_client['synPosDishes']['dishList'].find()
        for u in res:
            if u.get('diningNo') in diningNos:
                diningNo = u.get('diningNo')
                stallNo = u.get('stallNo')
                tempList = u.get('dishList')
                ttList = []
                for v in tempList:
                    v['diningNo'] = diningNo
                    v['stallNo'] = stallNo
                    ttList.append(v)
                dishList.extend(ttList)
        dd = []
        for v in dishList:
            data = {
                'diningNo': v.get('diningNo'),
                'stallNo': v.get('stallNo'),
                'dishName': v.get('dishName')
            }
            dd.append(data)
        return dd

    def test5(self):
        collection = self.mongo_client['synPosDining']['diningStallList']
        match_dict = {"$match": {'diningNo': '1'}}
        unwind_dict = {'$unwind': "$stallList"}
        group_dict = {
            '$group': {
                '_id': {
                    'diningNo': '$diningNo',
                    'stallNo': '$stallList.stallNo'
                },
                'diningName': {
                    '$addToSet': '$diningName'
                },
                'stallName': {
                    '$addToSet': '$stallList.stallName'
                }
            }
        }
        project_dict = {
            '$project': {
                '_id': 0,
                'diningNo': '$_id.diningNo',
                'stallNo': '$_id.stallNo',
                'diningName': '$diningName',
                'stallName': '$stallName'
            }
        }
        cursorcount = collection.aggregate(
            [match_dict, unwind_dict, group_dict, project_dict])
        for v in cursorcount:
            print('----------------------', v)

    def test6(self):
        '''
        {
            "dishState": "未通过/通过/待审核（fail/pass/stay）",
            "checkFailDesc": "审核未通过描述（通过描述默认填写""）",
            "dishIds": [
                {
                    "diningNo": "餐厅Id",
                    "stallNo": "档口Id",
                    "dishId": "菜品Id"
                },
                {
                    "diningNo": "餐厅Id", 
                    "stallNo": "档口Id", 
                    "dishId": "菜品Id" 
                }
            ]
        }
        '''
        collection = self.mongo_client['synPosDishes']['dishList']
        cond = {'diningNo': '1', 'stallNo': '1-1', 'dishList.dishId': '1'}
        update = {
            '$set': {
                'dishList.$.dishName': '狗蛋123',
                'dishList.$.dishState': '----------------------',
                'dishList.$.checkFailDesc': '============='
            }
        }
        dd = collection.find_one_and_update(cond, update)
        # dd = collection.find_one(cond)
        print(dd['checkFailDesc'])
        # cursorcount = collection.aggregate([])
        # for v in cursorcount:
        #     print('----------------------', v)

    def test7(self, imageSrc):
        # 按照固定宽度等比缩放base64格式图片
        imgData = base64.b64decode(imageSrc)
        nparr = np.frombuffer(imgData, np.uint8)
        img_np = cv2.imdecode(nparr, cv2.IMREAD_COLOR)
        (x, y, chan) = img_np.shape
        x_s = 200
        y_s = int(y * x_s / x)
        img_new = cv2.resize(img_np, (x_s, y_s))
        image = cv2.imencode('.jpg', img_new)[1]
        base64_data = str(base64.b64encode(image))[2:-1]
        collectionImage = self.mongo_client['zlll']['safe']
        imageInfo = {'imageId': '123', 'imageSrc': base64_data}
        collectionImage.insert_one(imageInfo)

    def test8(self):
        cond = {'imageId': '123'}
        collectionImage = self.mongo_client['zlll']['safe'].find_one(cond)
        imageSrc = collectionImage.get('imageSrc')
        print('---', len(imageSrc))
        imgData = base64.b64decode(imageSrc)
        nparr = np.frombuffer(imgData, np.uint8)
        img_np = cv2.imdecode(nparr, cv2.IMREAD_COLOR)
        cv2.imwrite('./dddd.jpg', img_np)

    def test9(self):
        startTime = '2021-11-02 00:00:00'
        endTime = '2021-11-08 23:59:59'
        bill_state = 1000
        pay_type = 1000
        start = datetime.strptime(startTime, "%Y-%m-%d %H:%M:%S")
        end = datetime.strptime(endTime, "%Y-%m-%d %H:%M:%S")
        if 1000 == bill_state and 1000 == pay_type:
            cond = {
                'createTime':{
                    '$gte': start,
                    '$lte': end
                }, 
                'deviceNo': 'TC14D16T40010'
            }
        elif 1000 == bill_state and 1000 != pay_type:
            cond = {
                'createTime':{
                    '$gte': start,
                    '$lte': end
                }, 
                'deviceNo': 'TC14D16T40010',
                'payType': pay_type
            }
        elif 1000 != bill_state and 1000 == pay_type:
            cond = {
                'createTime':{
                    '$gte': start,
                    '$lte': end
                }, 
                'deviceNo': 'TC14D16T40010',
                'billState': bill_state
            }
        else:
            cond = {
                'createTime':{
                    '$gte': start,
                    '$lte': end
                }, 
                'deviceNo': 'TC14D16T40010',
                'billState': bill_state,
                'payType': pay_type
            }

        collection = self.mongo_client['synPosBill']['billList']
        count = collection.find(cond).count()
        res = collection.find(cond).sort('createTime', -1).limit(3).skip(0)
        print('一共有:', count)
        for v in res:
            print('===', v.get('billId'), v.get('createTime'))
            # print(v)

    def test10(self):
        collection = self.mongo_client['synPosDining']['diningStallList']
        cond = {'diningNo': '756a72e8361411ecb64e0242ac110002'}
        # cond = {
        #     'diningNo': '756a72e8361411ecb64e0242ac110002',
        #     'stallList.stallName': {'$regex': '龙骨'}
        # }
        dd = collection.find(cond)
        for v in dd:
            stallList = v.get('stallList')
            for w in stallList:
                print('---', w.get('stallName'))
                
                
if __name__ == '__main__':
    t = MongoGridFS()
    # t.test8()
    t.test10()
    # dd = t.test4()
    # for v in dd:
    #     print('----------', v)
    # files_id = t.upLoadFile(file_coll='apk', file_name='山东大学_1.0.0_app.apk', version_desc='')
    # files_id = t.upLoadFile(file_coll='apk', file_name='山东大学_1.0.1_app.apk', version_desc='')
    # print('uploadFile files_id=', str(files_id), type(str(files_id)))
    # t.downLoadFile(file_coll='test_pdf', file_name='2.pdf', out_name='tt.pdf')
    # t.tt(file_coll='apk')